/**
 * 数据库初始化模块
 * @module db/init
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const config = require('../config/config');
const fs = require('fs');

// 确保数据库目录存在
const dbDir = path.dirname(config.dbPath);
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true });
}

/**
 * 创建数据库连接
 * @returns {sqlite3.Database} 数据库连接对象
 */
function createDbConnection() {
  return new sqlite3.Database(config.dbPath, (err) => {
    if (err) {
      console.error('数据库连接失败:', err.message);
    } else {
      console.log('已连接到SQLite数据库');
    }
  });
}

/**
 * 初始化数据库，创建所有必要的表
 */
function initDb() {
  const db = createDbConnection();

  // 启用外键约束
  db.run('PRAGMA foreign_keys = ON');

  // 创建用户表
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      email TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      is_pro BOOLEAN DEFAULT 0,
      is_admin BOOLEAN DEFAULT 0,
      pro_expiry_date TEXT,
      register_date TEXT NOT NULL,
      last_login TEXT
    )
  `);

  // 检查并添加is_admin列（如果不存在）
  db.all("PRAGMA table_info(users)", (err, rows) => {
    if (!err) {
      const adminColumnExists = rows.some(row => row.name === 'is_admin');
      if (!adminColumnExists) {
        db.run("ALTER TABLE users ADD COLUMN is_admin BOOLEAN DEFAULT 0", (err) => {
          if (err) {
            console.error('添加is_admin列失败:', err.message);
          } else {
            console.log('已添加is_admin列到用户表');
          }
        });
      }
    }
  });

  // 创建会员计划表
  db.run(`
    CREATE TABLE IF NOT EXISTS membership_plans (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      price REAL NOT NULL,
      currency TEXT NOT NULL,
      period TEXT NOT NULL,
      features TEXT NOT NULL,
      limits TEXT NOT NULL
    )
  `);

  // 创建交易记录表
  db.run(`
    CREATE TABLE IF NOT EXISTS transactions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      plan_id TEXT NOT NULL,
      amount REAL NOT NULL,
      currency TEXT NOT NULL,
      status TEXT NOT NULL,
      payment_method TEXT NOT NULL,
      order_id TEXT,
      created_at TEXT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users (id),
      FOREIGN KEY (plan_id) REFERENCES membership_plans (id)
    )
  `);

  // 创建工具使用统计表
  db.run(`
    CREATE TABLE IF NOT EXISTS usage_stats (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      tool_type TEXT NOT NULL,
      operation_type TEXT NOT NULL,
      metadata TEXT,
      used_at TEXT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 创建文件处理记录表
  db.run(`
    CREATE TABLE IF NOT EXISTS file_processes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      original_filename TEXT NOT NULL,
      processed_filename TEXT NOT NULL,
      process_type TEXT NOT NULL,
      created_at TEXT NOT NULL,
      file_size INTEGER NOT NULL,
      processed_size INTEGER,
      download_url TEXT,
      FOREIGN KEY (user_id) REFERENCES users (id)
    )
  `);

  // 关闭数据库连接
  db.close((err) => {
    if (err) {
      console.error('关闭数据库连接失败:', err.message);
    } else {
      console.log('数据库初始化完成，连接已关闭');
    }
  });
}

module.exports = {
  createDbConnection,
  initDb
}; 